/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package botoseis.mainGui.database;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.AbstractAction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.DefaultListModel;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JButton;
import javax.swing.JPanel;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartFrame;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.plot.XYPlot;
import org.jfree.chart.renderer.xy.StandardXYItemRenderer;
import org.jfree.chart.renderer.xy.XYLineAndShapeRenderer;
import org.jfree.data.xy.XYSeries;
import org.jfree.data.xy.XYSeriesCollection;

/**
 *
 * @author wogonowski
 */
public class DBDisplay extends javax.swing.JFrame {
    Connection con=null;
    ResultSet rs = null;
    PreparedStatement pst = null;
    final XYSeriesCollection dataset = new XYSeriesCollection();           

       /** The index of the last dataset added. */
    private int datasetIndex = 0;
    
    /** The plot. */
    private XYPlot plot;
    
    /**
     * Creates new form DBDisplay
     */
    public DBDisplay() {
        initComponents();
        
        modelP = new DefaultListModel();
        modelL = new DefaultListModel();
        modelAT = new DefaultListModel();
        modelA  = new DefaultListModel();
        setTitle("Database Utlilities");
        //Define model for jList
        jList1.setModel(modelP);
        jList2.setModel(modelL);
        jList3.setModel(modelAT);
        jList4.setModel(modelA);
         con=DBConnect.ConnectDb();
             try {  
                   modelP.clear();
                   modelL.clear();
                   modelAT.clear();
                   modelA.clear();
                   String queryP ="SELECT * FROM PROJECT";
                   pst=con.prepareStatement(queryP);
                   rs = pst.executeQuery();
                   
                while (rs.next()) { 
                    modelP.addElement(rs.getString("NAME"));  
                } 
                    
            } catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
                //con.close();
                }
                 catch (Exception e) {
                 }
             }                 
        
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {
        bindingGroup = new org.jdesktop.beansbinding.BindingGroup();

        jPanel2 = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jList1 = new javax.swing.JList();
        jScrollPane3 = new javax.swing.JScrollPane();
        jList2 = new javax.swing.JList();
        jScrollPane4 = new javax.swing.JScrollPane();
        jList3 = new javax.swing.JList();
        jScrollPane5 = new javax.swing.JScrollPane();
        jList4 = new javax.swing.JList();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        Delete = new javax.swing.JButton();
        Exit = new javax.swing.JButton();
        jPanel1 = new javax.swing.JPanel();
        DisplayChart = new javax.swing.JButton();
        AddToChart = new javax.swing.JButton();
        RemoveFromChart = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowClosing(java.awt.event.WindowEvent evt) {
                formWindowClosing(evt);
            }
        });

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder("Select database "));

        jList1.setBorder(null);
        jList1.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jList1.setMaximumSize(new java.awt.Dimension(160, 35700));
        jList1.setMinimumSize(new java.awt.Dimension(160, 200));
        jList1.setSelectionBackground(new java.awt.Color(255, 245, 185));
        jList1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jList1MouseClicked(evt);
            }
        });
        jScrollPane2.setViewportView(jList1);

        jList2.setBorder(null);
        jList2.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jList2.setSelectionBackground(new java.awt.Color(255, 245, 185));
        jList2.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jList2MouseClicked(evt);
            }
        });
        jScrollPane3.setViewportView(jList2);

        jList3.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jList3.setSelectionBackground(new java.awt.Color(255, 245, 185));
        jList3.setVisibleRowCount(7);
        jList3.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jList3MouseClicked(evt);
            }
        });
        jScrollPane4.setViewportView(jList3);

        jList4.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jList4.setSelectionBackground(new java.awt.Color(255, 245, 185));
        jList4.setVisibleRowCount(7);
        jScrollPane5.setViewportView(jList4);

        jLabel1.setBackground(new java.awt.Color(80, 113, 220));
        jLabel1.setForeground(new java.awt.Color(255, 255, 255));
        jLabel1.setText("  Project");
        jLabel1.setOpaque(true);

        jLabel2.setBackground(new java.awt.Color(80, 113, 220));
        jLabel2.setForeground(new java.awt.Color(255, 255, 255));
        jLabel2.setText("  Line");
        jLabel2.setOpaque(true);

        jLabel3.setBackground(new java.awt.Color(80, 113, 220));
        jLabel3.setForeground(new java.awt.Color(255, 255, 255));
        jLabel3.setText("  Type of attribute");
        jLabel3.setOpaque(true);

        jLabel4.setBackground(new java.awt.Color(80, 113, 220));
        jLabel4.setForeground(new java.awt.Color(255, 255, 255));
        jLabel4.setText("  Attributes");
        jLabel4.setOpaque(true);

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jLabel1, javax.swing.GroupLayout.DEFAULT_SIZE, 140, Short.MAX_VALUE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jLabel2, javax.swing.GroupLayout.DEFAULT_SIZE, 140, Short.MAX_VALUE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jScrollPane4, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jLabel3, javax.swing.GroupLayout.DEFAULT_SIZE, 139, Short.MAX_VALUE))
                .addGap(18, 18, Short.MAX_VALUE)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jScrollPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jLabel4, javax.swing.GroupLayout.DEFAULT_SIZE, 134, Short.MAX_VALUE))
                .addContainerGap())
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(jLabel2)
                    .addComponent(jLabel3)
                    .addComponent(jLabel4))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jScrollPane2, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 132, Short.MAX_VALUE)
                    .addComponent(jScrollPane3, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jScrollPane4, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 132, Short.MAX_VALUE)
                    .addComponent(jScrollPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE))
                .addGap(13, 13, 13))
        );

        Delete.setText("Delete attr");
        Delete.setToolTipText("Delete selected attribute database");
        Delete.setPreferredSize(new java.awt.Dimension(120, 29));
        Delete.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                DeleteActionPerformed(evt);
            }
        });

        Exit.setText("Exit");
        Exit.setPreferredSize(new java.awt.Dimension(120, 29));
        Exit.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                ExitActionPerformed(evt);
            }
        });

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Chart"));

        DisplayChart.setText("Display chart");
        DisplayChart.setToolTipText("Display XY chart of selected attribute database");
        DisplayChart.setHorizontalTextPosition(javax.swing.SwingConstants.CENTER);
        DisplayChart.setMaximumSize(new java.awt.Dimension(140, 29));
        DisplayChart.setMinimumSize(new java.awt.Dimension(140, 29));
        DisplayChart.setPreferredSize(new java.awt.Dimension(140, 29));
        DisplayChart.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                DisplayChartActionPerformed(evt);
            }
        });

        AddToChart.setText("Add to chart");

        org.jdesktop.beansbinding.Binding binding = org.jdesktop.beansbinding.Bindings.createAutoBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, AddToChart, org.jdesktop.beansbinding.ELProperty.create("Add"), AddToChart, org.jdesktop.beansbinding.BeanProperty.create("actionCommand"), "");
        bindingGroup.addBinding(binding);

        AddToChart.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                AddToChartActionPerformed(evt);
            }
        });

        RemoveFromChart.setText("Remove from chart");
        RemoveFromChart.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                RemoveFromChartActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(DisplayChart, javax.swing.GroupLayout.PREFERRED_SIZE, 130, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(AddToChart)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(RemoveFromChart)
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel1Layout.createSequentialGroup()
                .addGap(8, 8, 8)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(DisplayChart, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(AddToChart)
                    .addComponent(RemoveFromChart))
                .addGap(10, 10, 10))
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addContainerGap())
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(Delete, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 49, Short.MAX_VALUE)
                        .addComponent(Exit, javax.swing.GroupLayout.PREFERRED_SIZE, 84, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(33, 33, 33))))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addGap(12, 12, 12))
                    .addGroup(layout.createSequentialGroup()
                        .addGap(31, 31, 31)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(Delete, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(Exit, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))))
        );

        bindingGroup.bind();

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void ExitActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_ExitActionPerformed
        dispose();
    }//GEN-LAST:event_ExitActionPerformed

    private void DisplayChartActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_DisplayChartActionPerformed


        CreateSeries(jList4.getSelectedValue().toString());
           // dataset.addSeries(series);
                
            //String tableName=jList3.getSelectedValue().toString()+"_LID"+lid+"_AID"+aid;
           // String query="SELECT x,y FROM "+tableName+"";
            //JDBCXYDataset dataset = new JDBCXYDataset(DBConnect.ConnectDb(),query);
            JFreeChart chart=ChartFactory.createXYLineChart("", "", "", dataset, PlotOrientation.VERTICAL, true, true,false);
             XYPlot plot = (XYPlot) chart.getPlot();
   
             final XYLineAndShapeRenderer renderer = new XYLineAndShapeRenderer();
                    renderer.setSeriesLinesVisible(0,true);
                    renderer.setSeriesShapesVisible(0, true);
                    
              chart.setBackgroundPaint(Color.white);
              plot.setBackgroundPaint(new Color(0xffffe0));
              plot.setDomainGridlinesVisible(true);
              plot.setDomainGridlinePaint(Color.lightGray);
              plot.setRangeGridlinePaint(Color.lightGray);
              plot.setForegroundAlpha(0.5f);
              
              plot.setRangeZeroBaselinePaint(Color.gray);

             // plot.setRenderer(renderer);

        //CUSTOMIZE RANGE AXIS
        final NumberAxis rangeAxis = (NumberAxis) plot.getRangeAxis();
        //rangeAxis.setStandardTickUnits(NumberAxis.createIntegerTickUnits());
        rangeAxis.setAutoRangeIncludesZero(false);
        //rangeAxis.setVerticalTickLabels(true);
        //rangeAxis.setAutoTickUnitSelection(rootPaneCheckingEnabled);
        
         final NumberAxis domainAxis = (NumberAxis) plot.getRangeAxis();
         //        domainAxis.setLowerMargin(0.0D);
        //         domainAxis.setUpperMargin(0.0D);     
         
            ChartFrame fchart = new ChartFrame("Chart of database attribute",chart);
            
            fchart.setVisible(true);
            fchart.setSize(1000,450);      
        
    }//GEN-LAST:event_DisplayChartActionPerformed

    private void jList1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jList1MouseClicked
        
        try {      
                    con=DBConnect.ConnectDb();
                    modelL.clear();
                    modelAT.clear();
                    modelA.clear();
                   String project = jList1.getSelectedValue().toString();
                   String queryL="SELECT line.name as name FROM LINE INNER JOIN project ON (project.id=line.pid ) WHERE project.name='"+project+"' ";
                   pst=con.prepareStatement(queryL);
                   rs = pst.executeQuery();
                   while (rs.next()) { 
                   modelL.addElement(rs.getString("name"));  
                }           
            
        } catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
                //con.close();
                }
                 catch (Exception e) {
                 }
             }        
        
    }//GEN-LAST:event_jList1MouseClicked

    private void jList3MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jList3MouseClicked
    //Show attributes DB
        try {
                   con=DBConnect.ConnectDb();
                   modelA.clear();
                   String atype = jList3.getSelectedValue().toString();
                   String line = jList2.getSelectedValue().toString();
                   String queryA ="SELECT attr.NAME as NAME  FROM attr LEFT JOIN line ON (attr.lid=line.id) INNER JOIN  attr_type ON (attr.tid=attr_type.id ) WHERE attr_type.name='"+atype+"' and line.name='"+line+"'";
                   pst=con.prepareStatement(queryA);
                   rs = pst.executeQuery();
                   while (rs.next()) { 
                   modelA.addElement(rs.getString("NAME"));  
                }
        }catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
               //con.close();
                }
                 catch (Exception e) {
                 }
             }    
    }//GEN-LAST:event_jList3MouseClicked

    private void jList2MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jList2MouseClicked
             try {
                   con=DBConnect.ConnectDb();
                   modelAT.clear();
                   modelA.clear();
                   String line = jList2.getSelectedValue().toString();
                   String queryA ="select attr_type.NAME from attr_type WHERE attr_type.id IN (SELECT attr.tid FROM attr INNER JOIN line ON (attr.lid=line.id) WHERE line.name='"+line+"' GROUP BY attr.tid)";
                   pst=con.prepareStatement(queryA);
                   rs = pst.executeQuery();
                   while (rs.next()) { 
                   modelAT.addElement(rs.getString("NAME"));  
                }
        }catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
                 //con.close();
                }
                 catch (Exception e) {
                 }
             }    
    }//GEN-LAST:event_jList2MouseClicked

    private void formWindowClosing(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowClosing
         this.setVisible(false);
        //this.dispose();
       // this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        this.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
    }//GEN-LAST:event_formWindowClosing

    private void DeleteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_DeleteActionPerformed
        //Remove selected attribute form DB
                      // Check all selected fields    
                if (jList4.isSelectionEmpty()==true){
                JOptionPane.showMessageDialog(null,"Please select attribute database to remove");  
                return;
                    } else {
                    
 if (JOptionPane.showConfirmDialog(null,"\n DB attribute: "+jList4.getSelectedValue().toString()+" will be delete\nAre you sure?") == JOptionPane.YES_OPTION) {                   
                  
                    
             con=DBConnect.ConnectDb();
        try {
                   String Line = jList2.getSelectedValue().toString();
                   String atype = jList3.getSelectedValue().toString();
                   String aname=jList4.getSelectedValue().toString();
                   String queryA="SELECT attr.id as aid, line.id as lid FROM attr LEFT JOIN line ON (attr.lid=line.id) INNER JOIN attr_type ON (attr.tid=attr_type.id ) WHERE line.name='"+Line+"' and attr_type.name='"+atype+"' and attr.name='"+aname+"'";
                   pst=con.prepareStatement(queryA);
                   rs = pst.executeQuery();
                   rs.next();
                   int aid=rs.getInt("aid");
                   int lid=rs.getInt("lid");
                   pst.close();
                   rs.close();
                   String tableName=jList3.getSelectedValue().toString()+"_LID"+lid+"_AID"+aid;
                   String queryDrop="DROP TABLE IF EXISTS "+tableName+"";
                   pst=con.prepareStatement(queryDrop);
                   pst.executeUpdate();
                   String queryDel="DELETE FROM attr WHERE id='"+aid+"'";
                   pst=con.prepareStatement(queryDel);
                   pst.executeUpdate();
                   modelA.clear();
                   modelAT.clear();
                   JOptionPane.showMessageDialog(null,"Remove of Attribute: "+aname+" was successfully completed"); 
            }
             catch (Exception e) {  
            System.out.println("Remove attribute from database fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
                //con.close();
                }
                 catch (Exception e) {
                 }
                     }    
            }
                }            
    }//GEN-LAST:event_DeleteActionPerformed

    private void RemoveFromChartActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_RemoveFromChartActionPerformed
        
    }//GEN-LAST:event_RemoveFromChartActionPerformed

    private void AddToChartActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_AddToChartActionPerformed
         if (evt.getActionCommand().equals("Add")) {
            if (this.datasetIndex < 20) {
                this.datasetIndex++;
                this.plot.setDataset(this.datasetIndex, CreateSeries(jList4.getSelectedValue().toString()));
                this.plot.setRenderer(this.datasetIndex, new StandardXYItemRenderer());
            }
        }
        else if (evt.getActionCommand().equals("Del")) {
            if (this.datasetIndex >= 1) {
                this.plot.setDataset(this.datasetIndex, null);
                this.plot.setRenderer(this.datasetIndex, null);
                this.datasetIndex--;
            }
        }     
    }//GEN-LAST:event_AddToChartActionPerformed

    

  private XYSeriesCollection CreateSeries(final String name){
          
                    con=DBConnect.ConnectDb();
                    XYSeries series = new XYSeries(jList4.getSelectedValue().toString());
        try {

                   series.getDescription();
                   series.setDescription(jList4.getSelectedValue().toString());
                   String Line = jList2.getSelectedValue().toString();
                   String atype = jList3.getSelectedValue().toString();
                   String aname=jList4.getSelectedValue().toString();
                   String queryA="SELECT attr.id as aid, line.id as lid FROM ATTR LEFT JOIN line ON (attr.lid=line.id) INNER JOIN attr_type ON (attr.tid=attr_type.id ) WHERE line.name='"+Line+"' and attr_type.name='"+atype+"' and attr.name='"+aname+"'";
                   pst=con.prepareStatement(queryA);
                   rs = pst.executeQuery();
                   rs.next();
                   int aid=rs.getInt("aid");
                   int lid=rs.getInt("lid");
                   pst.close();
                   rs.close();
                 String tableName=jList3.getSelectedValue().toString()+"_LID"+lid+"_AID"+aid;
                 String query="SELECT x,y FROM "+tableName+"";
                 pst=con.prepareStatement(query);
                 rs = pst.executeQuery();    
            while (rs.next()) {  
                int x = rs.getInt("x");  
                double y = rs.getDouble("y");  
                series.add(x, y);  
            }  

            dataset.addSeries(series);  
        } catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
        }  
 
   return new XYSeriesCollection(series);
}
    

    
    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(DBDisplay.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(DBDisplay.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(DBDisplay.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(DBDisplay.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new DBDisplay().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    public javax.swing.JButton AddToChart;
    private javax.swing.JButton Delete;
    private javax.swing.JButton DisplayChart;
    private javax.swing.JButton Exit;
    private javax.swing.JButton RemoveFromChart;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JList jList1;
    private javax.swing.JList jList2;
    private javax.swing.JList jList3;
    private javax.swing.JList jList4;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JScrollPane jScrollPane4;
    private javax.swing.JScrollPane jScrollPane5;
    private org.jdesktop.beansbinding.BindingGroup bindingGroup;
    // End of variables declaration//GEN-END:variables
    private DefaultListModel modelP; 
    private DefaultListModel modelL;
    private DefaultListModel modelAT;
    private DefaultListModel modelA;
}
